using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Data.OleDb;
using Integrator.DAL.Excel.SpreadSheet;
using Excel;

namespace Integrator.DAL
{
    public static class ExcelHelper
    {
        /// <summary>
        /// Update data from file excel to DataTable
        /// </summary>
        /// <param name="filePath">path of file excel 2003, or file excel 2007</param>
        /// <param name="isReadColumnName">if value is true, read first row as coloumn of table</param>
        /// <param name="table"> return table contain excel data</param>
        /// <returns>return true if get data success, other return false</returns>
        public static bool GetDataTableFromExcelFile(string filePath, bool isReadColumnName, out DataTable table)
        {
            if (filePath.EndsWith(".xls"))
                return GetDataTableFromExcelFile2003(filePath, isReadColumnName, true, out table);
            return GetDataTableFromExcelFile2007(filePath, isReadColumnName, true, out table);
        }

        /// <summary>
        /// Get data from file excel 2003 to DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="isReadColumnName"></param>
        /// <param name="tryExcel2003WhenError"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        private static bool GetDataTableFromExcelFile2003(string filePath, bool isReadColumnName, bool tryExcel2003WhenError, out DataTable table)
        {

            FileStream stream = null;
            DataSet result = null;
            IExcelDataReader excelReader = null;
            try
            {
                stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                excelReader.IsFirstRowAsColumnNames = isReadColumnName;
                result = excelReader.AsDataSet();
                table = result.Tables[0];
            }
            catch (Exception ex)
            {
                if (tryExcel2003WhenError)
                    GetDataTableFromExcelFile2007(filePath, isReadColumnName, false, out table);
                else
                {
                    table = null;
                    return false;
                }
            }
            finally
            {
                try
                {
                    if (excelReader != null)
                        excelReader.Close();
                }
                catch { }
                try
                {
                    if (stream != null)
                        stream.Close();
                }
                catch { }
            }
            return true;
        }

        /// <summary>
        /// Get data from file excel 2007 to DataTable
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="isReadColumnName"></param>
        /// <param name="tryExcel2007WhenError"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        private static bool GetDataTableFromExcelFile2007(string filePath, bool isReadColumnName, bool tryExcel2007WhenError, out DataTable table)
        {
            FileStream stream = null;
            DataSet result = null;
            IExcelDataReader excelReader = null;
            try
            {
                stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                excelReader.IsFirstRowAsColumnNames = isReadColumnName;
                result = excelReader.AsDataSet();
                table = result.Tables[0];
                return true;
            }
            catch (Exception ex)
            {
                if (tryExcel2007WhenError)
                    GetDataTableFromExcelFile2003(filePath, isReadColumnName, false, out table);
                else
                {
                    table = null;
                    return false;
                }
            }
            finally
            {
                try
                {
                    if (excelReader != null)
                        excelReader.Close();
                }
                catch { }
                try
                {
                    if (stream != null)
                        stream.Close();
                }
                catch { }
            }
            return true;
        }

        /// <summary>
        /// Populate all data (all converted into String) in all worksheets 
        /// from a given Excel workbook.
        /// </summary>
        /// <param name="filePath">File path of the Excel workbook</param>
        /// <returns>DataSet with all worksheet populate into DataTable</returns>
        public static DataSet CreateDataSet(String filePath)
        {
            return CreateDataSet(filePath, true, true);
        }

        /// <summary>
        /// Populate all data (all converted into String) in all worksheets 
        /// from a given Excel workbook.
        /// </summary>
        /// <param name="filePath">File path of the Excel workbook</param>
        /// <returns>DataSet with all worksheet populate into DataTable</returns>
        public static DataSet CreateDataSet(String filePath, bool firsRowIsColumnNames, bool idxRowIsColumnNames)
        {
            DataSet ds = new DataSet();
            Workbook workbook = Workbook.Open(filePath);
            foreach (Worksheet ws in workbook.Worksheets)
            {
                DataTable dt = PopulateDataTable(ws, firsRowIsColumnNames, idxRowIsColumnNames);
                ds.Tables.Add(dt);
            }
            return ds;
        }


        /// <summary>
        /// Populate data (all converted into String) from a given Excel 
        /// workbook and also work sheet name into a new instance of DataTable.
        /// Returns null if given work sheet is not found.
        /// </summary>
        public static DataTable CreateDataTable(String filePath, String sheetName)
        {
            return CreateDataTable(filePath, sheetName);
        }

        /// <summary>
        /// Populate data (all converted into String) from a given Excel 
        /// workbook and also work sheet name into a new instance of DataTable.
        /// Returns null if given work sheet is not found.
        /// </summary>
        /// <param name="filePath">File path of the Excel workbook</param>
        /// <param name="sheetName">Worksheet name in workbook</param>
        /// <returns>DataTable with populate data</returns>
        public static DataTable CreateDataTable(String filePath, String sheetName, bool firstRowIsColumnNames, bool idxRowIsColumnNames)
        {
            Workbook workbook = Workbook.Open(filePath);
            foreach (Worksheet ws in workbook.Worksheets)
            {
                if (ws.Name.Equals(sheetName))
                    return PopulateDataTable(ws, firstRowIsColumnNames, idxRowIsColumnNames );
            }
            return null;
        }

        private static DataTable PopulateDataTable(Worksheet ws, bool firsRowIsColumnNames, bool idxRowIsColumnNames)
        {
            CellCollection Cells = ws.Cells;

            // Creates DataTable from a Worksheet
            // All values will be treated as Strings
            DataTable dt = new DataTable(ws.Name);
            int iStart = 0;
            // Extract columns
            if (firsRowIsColumnNames)
            {
                iStart = 1;
                for (int i = 0; i <= Cells.LastColIndex; i++)
                    dt.Columns.Add(Cells[0, i].StringValue, typeof(String));
            }
            else
            {
                for (int i = 0; i <= Cells.LastColIndex; i++)
                    dt.Columns.Add("Col_" + i.ToString(), typeof(String));
            }

            //Kiem tra doc hang chi dan
            if (idxRowIsColumnNames) iStart = 2;

            // Extract data
            for (int currentRowIndex = iStart; currentRowIndex <= Cells.LastRowIndex; currentRowIndex++)
            {
                DataRow dr = dt.NewRow();
                for (int currentColumnIndex = 0; currentColumnIndex <= Cells.LastColIndex; currentColumnIndex++)
                    dr[currentColumnIndex] = Cells[currentRowIndex, currentColumnIndex].StringValue;
                dt.Rows.Add(dr);
            }

            return dt;
        }

        /// <summary>
        /// Populate all data from the given DataSet into a new Excel workbook
        /// </summary>
        public static void CreateWorkbook(String filePath, DataSet dataset)
        {
            CreateWorkbook(filePath, dataset, false);
        }
        /// <summary>
        /// Populate all data from the given DataSet into a new Excel workbook
        /// </summary>
        /// <param name="filePath">File path to new Excel workbook to be created</param>
        /// <param name="dataset">Source DataSet</param>
        public static void CreateWorkbook(String filePath, DataSet dataset, bool firstRowColumnNames)
        {
            Workbook workbook = CreateWorkbook(dataset, firstRowColumnNames);
            workbook.Save(filePath);
        }


        /// <summary>
        /// Get the workbook from the dataset
        /// </summary>
        /// <param name="dataset">Source DataSet</param>        
        public static Workbook CreateWorkbook(DataSet dataset, bool firstRowColumnNames)
        {
            if (dataset.Tables.Count == 0)
                throw new ArgumentException("DataSet needs to have at least one DataTable", "dataset");

            Workbook workbook = new Workbook();
            foreach (DataTable dt in dataset.Tables)
            {
                Worksheet worksheet = new Worksheet(dt.TableName);
                int iStart = 0;
                if (firstRowColumnNames)
                {
                    iStart = 1;
                }
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    // Add column header                    
                    if (firstRowColumnNames)
                    {
                        worksheet.Cells[0, i] = new Cell(dt.Columns[i].ColumnName);
                    }
                    // Populate row data
                    for (int j = 0; j < dt.Rows.Count; j++)
                        if (firstRowColumnNames)
                        worksheet.Cells[j+1, i] = new Cell(dt.Rows[j][i].ToString());
                }
                workbook.Worksheets.Add(worksheet);
            }

            return workbook;
        }        
    }
}
